Starburst
Querying Overview
General information on querying Starburst can be found at
and
As a SQL oriented database the general structure of the queries is
SELECT …
from …
WHERE …
ORDER BY …
Starburst provides a robust set of capabilities to handle modern data types such as JSON and data arrays. Qarbine naturally handles answer sets with these dynamic data types.
JSON Queries
For this JSON discussion consider a connected MongoDB Atlas instance with a database ‘q_sample’ and a collection ‘portfolio’. A sample element is shown below.
{_class: "biz_model_Portfolio", who: 'John Doe', _id: 1, isIRA: false,
addressItems: ['910 Marcall Street', 'Chicago, IL', '80512' ],
opened: new ISODate("2016-03-15T14:00:00Z"),
cash: 45000,
goal: 'An asset mix seeking income and the potential for capital appreciation which can withstand moderate market fluctuations.',
profile: {
phone: '801-677-8823',
email: 'johnnydoe@aol.com',
email_alerts: false,
go_green: false
},
stocks: [
{_class: "biz_model_Stock", sector: 'CPG', name: 'Coca-Cola', ticker: 'KO', shares: 500, basis: 45, price: 47.25, purchased": new ISODate("2016-03-15T00:00:00Z") }...
],
bonds: [
{_class: "biz_model_Bond", issuer: 'US Treasury 10 year', value: 40000, yield: 0.0325, purchased": new ISODate("2017-08-15T00:00:00Z") }...
]
}
In the Starburst Query Editor running the query
select *
from q_sample.portfolio
returns 5 rows. Sample content split in half is shown below.
The right hand side is shown below.
Notice that the original MongoDB field names have been lower cased. The MongoDB embedded documents (i.e., profile) and the embedded arrays (addressitems, stocks and bonds) are returned as strings in the Starburst Query Editor.
WIthin Qarbine, the query
SELECT *
FROM myjsonrw.q_sample.portfolio
returns this left half
and this right half.
Viewing the details of the first row shows the following.
 |  |
Notice the original embedded documents and arrays are returned in their native JSON object form rather than simple strings. The Qarbine form is much easier for analytic processing.
In some cases you can use dot notation for the SELECT and the WHERE clauses as shown below.
select *, profile.go_green
from q_sample.portfolio
WHERE profile.go_green = true
An alternative is shown below using the JSON_EXTRACT function.
SELECT *, JSON_EXTRACT_SCALAR(profile, '$.go_green')
FROM q_sample.portfolio
WHERE JSON_EXTRACT_SCALAR(profile, '$.go_green') = 'true'
Trino supports three functions for querying JSON data all of which use JSON path notation: json_exists, json_query, and json_value. . Trino also supports two functions for generating JSON data – json_array, and json_object. Information on using JSON oriented queries can be found at
https://docs.Starburst .io/Starburst -galaxy/reference/sql/ref/functions/json.html
When your queries return simple JSON strings rather than JSON objects and you want the latter the use the Qarbine ‘pragma’ statement
#pragma convertToObject columnsCSV
See the Data Source Designer guide for details on using pragmas to manipulate answer sets.
Array Handling
Starburst allows you to easily interact and create arrays. For example, this query
SELECT ARRAY[4, 5, 6] AS integers,
ARRAY['hello', 'world'] AS varchars;
returns JSON arrays as shown below.
| integers | varchars |
|---|---|
| [4, 5, 6] | [hello, world] |
The SQL array indexes are 1-based. For more Array function information see https://docs.Starburst .io/Starburst -galaxy/reference/sql/ref/functions/array.html
As noted above, the results in Qarbine are the natural array objects which facilitates analytic processing. Qarbine has several macro functions to interact with arrays and the layout of Qarbine templates naturally handles Starburst returned arrays.
Qarbine Virtual Queries
There are a few convenience queries which are mainly DBA oriented. These queries are recognized by the Qarbine driver and provide common database information. Any catalog and schema set in the data service definition constrains what is returned. For example, if a catalog is given in the data service, then only schemas in that one catalog are returned.
These virtual query defaults are independent of whatever drop down option is chosen in the Data Source Designer tool. If a specific schema’s information is wanted for example, it must be explicitly given.
| Query | Description |
|---|---|
| list catalogs | Return a list of catalogs. |
| list schemas [CATALOG] | Return a list of schemas. Optionally provide a catalog name when there is none in the data service. |
| describe schema [SCHEMA]describe schema [CATALOG.SCHEMA] | Return the details of the given schema. The optional argument may be of the form “schema” or “catalog.schema”. |
| list tables [SCHEMA]list tables [CATALOG.SCHEMA] | Return a list of tables. The optional argument may be of the form “schema” or “catalog.schema”. |
| describe tables [SCHEMA]describe tables [CATALOG.SCHEMA] | Provide details on all of the tables. The optional argument may be of the form “schema” or “catalog.schema”. This may take a while depending on your database structure. |
| describe table TABLEdescribe table [SCHEMA.TABLE] | Provide details on the given table. The argument may be of the form “schema.table” or “table”. |
See the “DBA Productivity” section of the online documentation for more details.
Troubleshooting
Use the Starburst query tools to diagnose issues. It is available using the sidebar navigation shown below.
In the Data Source Designer pressing Alt and clicking the run image returns the effective query that would be sent to Starburst. This query has had all variables and macro functions evaluated by this point.